package com.example.exercise.excel.util;

import com.codoid.products.fillo.Connection;
import com.codoid.products.fillo.Fillo;
import com.codoid.products.fillo.Recordset;

/**
 * excel SQL处理方式
 */
class ExcelFillo {
    private static final String excelPath = "E://test-sources/User.xlsx";
    public static void select(String excelPath) throws Exception {
        Fillo fillo = new Fillo() ;
        Connection conn = fillo.getConnection(excelPath);
        String strQuery = "Select * from baseInfo where name='陈浩然'";
        Recordset rs = conn.executeQuery(strQuery);
        while (rs.next()) {
            System.err.printf("%s\t%s\t%s\t%s\t%s\t%n",
                    rs.getField("id"),
                    rs.getField("name"),
                    rs.getField("age"),
                    rs.getField("email"),
                    rs.getField("idNo"));
        }
        // 多个Where条件
        rs = conn.executeQuery("Select * from baseInfo where name='杨晨曦' and age='52'");
        while (rs.next()) {
            System.out.printf("%s\t%s\t%s\t%s\t%s\t%n",
                    rs.getField("id"),
                    rs.getField("name"),
                    rs.getField("age"),
                    rs.getField("email"),
                    rs.getField("idNo"));
        }
        // 通过方法构建where条件
        rs = conn.executeQuery("Select * from baseInfo")
                .where("name='黄俊杰'")
                .where("idNo='420105200101011000'");
        while (rs.next()) {
            System.err.printf("%s\t%s\t%s\t%s\t%s\t%n",
                    rs.getField("id"),
                    rs.getField("name"),
                    rs.getField("age"),
                    rs.getField("email"),
                    rs.getField("idNo"));
        }
        rs.close();
        conn.close();
    }

    public static void insert(String excelPath) throws Exception {
        Fillo fillo = new Fillo();
        Connection conn = fillo.getConnection(excelPath);
        String strQuery = "INSERT INTO baseInfo (id, name, age, email, idNo) VALUES (9, 'Pack张三', 33, 'pack@qq.com', '5555533336666')";
        conn.executeUpdate(strQuery);
        conn.close();
    }

    public static void update(String excelPath) throws Exception {
        Fillo fillo = new Fillo();
        Connection conn = fillo.getConnection(excelPath);
        String sql = "Update baseInfo Set name='Spring Boot3实战案例200讲' where id=9";
        conn.executeUpdate(sql);
        conn.close();
    }

    public static void delete(String excelPath) throws Exception {
        Fillo fillo = new Fillo();
        Connection conn = fillo.getConnection(excelPath);
        String strQuery = "DELETE FROM baseInfo where id=9";
        conn.executeUpdate(strQuery);
        conn.close();
    }

    public static void likeQuery(String excelPath) throws Exception {
        Fillo fillo = new Fillo();
        Connection conn = fillo.getConnection(excelPath);
        String strQuery = "Select * from baseInfo where name like '%陈%'";
        Recordset rs = conn.executeQuery(strQuery);
        while (rs.next()) {
            System.err.printf("%s\t%s\t%s\t%s\t%s\t%n",
                    rs.getField("id"),
                    rs.getField("name"),
                    rs.getField("age"),
                    rs.getField("email"),
                    rs.getField("idNo"));
        }
        rs.close();
        conn.close();
    }

    public static void distinctOperator(String excelPath) throws Exception {
        Fillo fillo = new Fillo();
        Connection conn = fillo.getConnection(excelPath);
        String strQuery = "Select DISTINCT name from baseInfo";
        Recordset rs = conn.executeQuery(strQuery);
        while (rs.next()) {
            System.err.printf("%s%n", rs.getField("name"));
        }
        rs.close();
        conn.close();
    }

    public static void setRowAndColumn(String excelPath) throws Exception {
        // Table start row
        System.setProperty("ROW", "1");
        // Table start column
        System.setProperty("COLUMN", "1");
        Fillo fillo = new Fillo();
        Connection conn = fillo.getConnection(excelPath);
        String strQuery = "Select * from baseInfo";
        Recordset rs = conn.executeQuery(strQuery);
        while (rs.next()) {
            System.err.printf("%s%n", rs.getField("name"));
        }
        rs.close();
        conn.close();
    }
        
    public static void main(String[] args) throws Exception{
        select(excelPath);
        insert(excelPath);
        update(excelPath);
        delete(excelPath);
        likeQuery(excelPath);
        distinctOperator(excelPath);
        setRowAndColumn(excelPath);
    }
}
